﻿#
#用于初始化允许零库存出货的未拆分替代品或未转换临期品
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#手工在命令行运行 ruby fix_icbill.rb
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    priceHashP = {}; #Price hash in PSI goods_id

    #准备待登记商品表
    sql1 = "select * from psi.t_goods where record_status = 1000 and ( 
		category_id !='0B289DCA-8AA6-0EBC-8AAC-2FBD5338AE60'
		and category_id != '2F79DC03-2C7F-F9E4-90C5-FD55E6C93C8C' 
		and category_id != '39208251-E27E-11E8-8B92-6C92BF475963' 
		and category_id != '7FFC09B8-C1AE-B47A-FFCD-7EC138408D32' 
		and category_id != 'AD84B25E-B225-FEB4-1DEC-5E19941D73C7' 
		and category_id != 'BA7DAD58-F064-33DC-A3B2-922A9B9B1BA6'  
	);"
    res1 = rds.query(sql1)
    res1.each do |tgr|
        code = tgr['code']
        id = tgr['id']
        name = tgr['name']
        purchase_price = tgr['purchase_price'];
        priceHashP.store(id,purchase_price);
    end
    puts priceHashP.count

    #search goods already in warehouse which id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' #汉溪总仓
    sql2 = "select * from psi.t_inventory where warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963';"
    res2 = rds.query(sql2)

    res2.each do |tgr|
        #从待登记表中移除已经存在于仓库总账的goods_id
        goods_id = tgr['goods_id']
        priceHashP.delete(goods_id)
    end
    puts priceHashP.count

    priceHashP.each { |goods_id, purchase_price|
            #3CFCB179-F707-11E8-8932-6C92BF475963 特定的入库单 ref#20081203006
            #9788CED3-F728-11E8-8932-6C92BF475963 特定的入库单 ref#20081204001
            #6437BAB7-F729-11E8-8932-6C92BF475963 特定的入库单 ref#20081204002
            #0ED63231-F72B-11E8-8932-6C92BF475963 特定的入库单 ref#20081204003
            goods_price=sprintf("%.2f",purchase_price)
            sqlu = "insert into psi.t_pw_bill_detail( 
                        id, date_created, 
                        goods_id, goods_count, goods_money, goods_price, 
                        pwbill_id, show_order, data_org, company_id
                    ) values( 
                        UPPER(UUID()), CURRENT_TIMESTAMP(), 
                        '#{goods_id}',0.0,0.0,#{goods_price},
                        '0ED63231-F72B-11E8-8932-6C92BF475963',
                        0,'01010001','4D74E1E4-A129-11E4-9B6A-782BCBD7746B'
                    );"
            resu = rds.query(sqlu)
    }

rds.close
